﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DAO_ADMIN;
using System.Data.SqlClient;
namespace PTTK19.DB
{
    public class DBNguyenLieu
    {
        //Lay danh sach nguyen lieu theo tung mon an
        public static DataTable getListNguyenLieuTheoMonAn(int _MaMonAn)
        {
            List<SqlParameter> parr = new List<SqlParameter>();
            string procname = "sp_LayDanhSachNguyenLieuTheoMonAn";
            parr.Add(new SqlParameter("@MaMonAn", _MaMonAn));
            DataTable dt = DataProvider.SelectStoreProcedure(procname, parr);
            return dt;
        }
        //lay danh sach nguyen lieu
        public static DataTable getListNguyenLieu(int _MaMonAn)
        {
            string procname = "sp_LayDanhSachNguyenLieu";
            List<SqlParameter> parr = new List<SqlParameter>();
            parr.Add(new SqlParameter("@MaMonAn", _MaMonAn));
            DataTable dt = DataProvider.SelectStoreProcedure(procname, parr);
            return dt;
        }
        //Lay danh sach don vi tinh
        public static DataTable getListDonViTinh()
        {
            string procname = "sp_LayDanhSachDonViTinh";
            DataTable dt = DataProvider.SelectStoreProcedure(procname);
            return dt;
        }
        //Lay so luong nguyen lieu ton kho
        public static DataTable getSoLuongTonKho(int _MaNguyenLieu)
        {
            string procname = "sp_SoLuongNguyenLieuTonKho";
            List<SqlParameter> parr = new List<SqlParameter>();
            parr.Add(new SqlParameter("@MaNguyenLieu", _MaNguyenLieu));
            DataTable dt = DataProvider.SelectStoreProcedure(procname, parr);
            return dt;
        }
        //Lay so luong nguyen lieu da dung
        public static DataTable getSoLuongDaDung(int _MaNguyenLieu)
        {
            string procname = "sp_TongSoLuongNguyenLieuDaDung";
            List<SqlParameter> parr = new List<SqlParameter>();
            parr.Add(new SqlParameter("@MaNguyenLieu", _MaNguyenLieu));
            DataTable dt = DataProvider.SelectStoreProcedure(procname, parr);
            return dt;
        }
        //Kiem tra hang ton kho
        public static int KiemTraTonKho(int _MaNguyenLieu)
        {
            int kq = 1;
            string _SoLuongTonKho = getSoLuongTonKho(_MaNguyenLieu).Rows[0][0].ToString();
            string _SoLuongDaDung = getSoLuongDaDung(_MaNguyenLieu).Rows[0][0].ToString();
            if (_SoLuongTonKho == "" || _SoLuongTonKho == null)
            {
                _SoLuongTonKho = "0";
            }
            if (_SoLuongDaDung == "" || _SoLuongDaDung == null)
            {
                _SoLuongDaDung = "0";
            }
            if (int.Parse(_SoLuongTonKho) < int.Parse(_SoLuongDaDung))
            {
                kq = 0;
            }
            return kq;
        }
        public static DataTable LayMaNguyenLieu()
        {
            DataTable ds = new DataTable();
            DBMS dbms = new DBMS();
            dbms.Connect();
            string sql = "SELECT MaNguyenLieu, TenNguyenLieu FROM NguyenLieu";
            dbms.Connect();
            ds = dbms.ExcuteQueryTable(sql);
            dbms.Disconnect();
            return ds;
        }

        internal static DataTable ThongKeNguyenLieuTheoThang(string thang)
        {
            string[] ngaythangdulieu = { "0", "31", "28", "31", "30", "31", "30", "31", "31", "30", "31", "30", "31" };
            int tam = int.Parse(thang.Substring(0, thang.IndexOf('/')));
            string startdate = "01/" + thang;
            string enddate = ngaythangdulieu[tam] + "/" + thang;
            DateTime ngaydau = Convert.ToDateTime(startdate);
            DateTime ngaycuoi = Convert.ToDateTime(enddate);
            List<SqlParameter> parr = new List<SqlParameter>();
            string procname = "ThongKeNVLThang";
            parr.Add(new SqlParameter("@ngaydau", ngaydau));
            parr.Add(new SqlParameter("@ngaycuoi", ngaycuoi));

            DataTable dt = DataProvider.SelectStoreProcedure(procname, parr);
            return dt;
        }
    }
}
